package com.ldzy.webase.dao;

import com.ldzy.webase.entity.Notice;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * @author ：Lq
 * @version ：1.0
 * @date ：2022/11/19 21:51
 * @description ：t_notice/t_notice_relation-消息表/消息与用户关系表-数据层
 */
public interface NoticeMapper {
    // 根据学生uid查询用户需要接收的所有通知-有联合索引[noticeRelationId和uid组成联合索引]
    // 1. not in查询，根据学生id查询t_notice_relation表里是否有包含对应noticeId的通知，包含且已经删除则不予发送
    // 2. 如果用户注册时间大于通知时间则不予发送
    // 3. 如果t_notice_relation表里的通知记录已被删除则不予发送
    @Select("select n.*,(select isRead from t_notice_relation where uid = #{uid} and noticeId = n.noticeId) as isRead from t_notice as n " +
            "where n.noticeId " +
            "not in(select noticeId from t_notice_relation where uid = #{uid} and isDelete = #{uid}) and " +
            "n.createDate >= (select createTime from t_user where uid = #{uid});")
    List<Notice> getAllNoticeByUid(@Param("uid") Integer uid);

    // 当用户点击阅读消息之后，直接根据用户接收的通知noticeId和学生uid添加对应消息记录到t_notice_relation表中，默认设置为已阅读状态[1]
    @Insert("insert into t_notice_relation(noticeId,uid) values(#{noticeId},#{uid});")
    int addNoticeRelation(@Param("noticeId") Integer noticeId,@Param("uid") Integer uid);

    // 当用户点击删除/清空消息时，根据noticeId和uid将isDelete字段修改为1
    @Update("update t_notice_relation set isDelete=0 where noticeId=1 and uid=1")
    int editNoticeRelation(@Param("noticeId") Integer noticeId,@Param("uid") Integer uid);
}
